package cn.liu.poi;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.HashMap;
import java.util.Map;

/**
 * @author LIU
 * @version 1.0.0
 * @date 2025/7/3
 */
public class PoiFormulaExample {
    public static void main(String[] args) {
//        extracted();


        Map<String, Double> map = new HashMap<>();
        map.put("G2",2.47);
        map.put("J2",0.240497071);
        map.put("E2",110.5675662);

        double v = formulaComputing(map,"92903.04*2*G2*(1+J2)/E2/E2");

//        Map<String, Double> map = new HashMap<>();
//        map.put("A1", 10.0);
//        map.put("B1", 22.0);
//
//        double v = formulaComputing(map, "AVERAGE(A1:B1)");
        System.out.println("  value = " + v);
    }

    private static double formulaComputing(Map<String, Double> map, String formula) {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        for (Map.Entry<String, Double> entry : map.entrySet()) {
            String key = entry.getKey();
            Double value = entry.getValue();
            // 设置值
            setCellValue(sheet, key, value);
        }


        // 获取物理行数（实际存在的行数）
        int rowIndex = sheet.getPhysicalNumberOfRows()+1;
        short colIndex = 0;


        // 设置单元格值
        Cell cell = sheet.createRow(rowIndex).createCell(colIndex);
        cell.setCellFormula(formula);
        System.out.printf("行索引 %s  列索引 %s  值 %s", rowIndex, colIndex, formula);

        // 计算所有公式
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);


        if (cellValue.getCellType().equals(CellType.ERROR)) {
            byte errorValue = cellValue.getErrorValue();
            String errorString = FormulaError.forInt(errorValue).getString();
            throw new IllegalArgumentException(errorString);
        }

        // 关闭工作簿
        try {
            return cellValue.getNumberValue();
        }finally {
            // 关闭工作簿
            try {
                workbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static void setCellValue(Sheet sheet, String key, Double value) {
        CellReference cellReference = new CellReference(key);
        int rowIndex = cellReference.getRow();
        short colIndex = cellReference.getCol();

        // 设置单元格值
        Row row = sheet.getRow(rowIndex);
        if (row == null) row = sheet.createRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        if (cell == null) cell = row.createCell(colIndex);
        cell.setCellValue(value);

        System.out.printf("行索引 %s  列索引 %s  值 %s", cellReference.getRow(), cellReference.getCol(), value);
        System.out.println();
    }


    private static void extracted() {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        // 设置单元格值
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(10.0);  // A1 = 10
        row.createCell(1).setCellValue(20.0);  // B1 = 20

        // 设置公式单元格
        Row formulaRow = sheet.createRow(10);

        // 设置更多公式
        formulaRow.createCell(0).setCellFormula("SUM(A1:B1)");  // A2 = SUM(A1:B1)
        formulaRow.createCell(1).setCellFormula("A1*B1");  // B2 = A1*B1
        formulaRow.createCell(2).setCellFormula("AVERAGE(A1:B1)");  // C2 = AVERAGE(A1:B1)
        formulaRow.createCell(3).setCellFormula("IF(1>2, \"A更大\", \"B更大或相等\")");  // D2 = IF比较


        // 计算所有公式
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 获取计算结果
        System.out.println("SUM结果: " + formulaRow.getCell(0).getNumericCellValue());
        System.out.println("乘积结果: " + formulaRow.getCell(1).getNumericCellValue());
        System.out.println("平均值: " + formulaRow.getCell(2).getNumericCellValue());
        System.out.println("IF结果: " + formulaRow.getCell(3).getStringCellValue());

        // 关闭工作簿
        try {
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private static void extracted1() {

        Workbook wb = new HSSFWorkbook();
        // 计算所有公式
        HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        // 计算单个行
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
        }
    }


}
